﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports WSEntidades
Imports Utilerias
Imports WSFactory
Imports Npgsql
Imports MySql.Data.MySqlClient
Public Class ProveedoresDAL
    Private cadConex As String = ""

    Public Sub New(ByVal pCadConex As String)
        Me.cadConex = pCadConex
    End Sub

#Region "FuncionesUtilizables"

    Public Function existeProveedor(ByVal pCvePro As String) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM proveedores " & _
                                            "WHERE CvePro = ?CvePro;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("?CvePro", pCvePro)
                    myConn.Open()
                    Return (Convert.ToInt16(myComm.ExecuteScalar()) > 0)
                End Using
            End Using
        Catch ex As Exception
            Throw
        Finally
        End Try
    End Function

    Public Function registraProveedor(ByVal pProveedor As ProveedoresBE) As Boolean

        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "INSERT INTO proveedores(CvePro,RfcPro, RazonSocial, " & _
                                                                "Calle, NoExterior, NoInterior, Colonia, Localidad, Referencia, Municipio, Estado, Pais, " & _
                                                                "CodigoPostal, Telefono1, Telefono2, Fax, CorreoElect, DirInternet, Contacto, Zona, Grupo, Status) " & _
                                            "VALUES (@CvePro, @RfcPro, @RazonSocial, @Calle, @NoExterior, @NoInterior, " & _
                                                    "@Colonia, @Localidad, @Referencia, @Municipio, @Estado, @Pais, @CodigoPostal, @Telefono1, Telefono2, @Fax, " &
                                                    "@CorreoElect, @DirInternet, @Contacto, @Zona, @Grupo, @Status);"
                    myComm.CommandType = System.Data.CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@CvePro", pProveedor.CvePro)
                    myComm.Parameters.AddWithValue("@RfcPro", pProveedor.RfcPro)
                    myComm.Parameters.AddWithValue("@RazonSocial", pProveedor.RazonSocial)
                    myComm.Parameters.AddWithValue("@Calle", pProveedor.Calle)
                    myComm.Parameters.AddWithValue("@NoExterior", pProveedor.NoExterior)
                    myComm.Parameters.AddWithValue("@NoInterior", pProveedor.NoInterior)
                    myComm.Parameters.AddWithValue("@Colonia", pProveedor.Colonia)
                    myComm.Parameters.AddWithValue("@Localidad", pProveedor.Localidad)
                    myComm.Parameters.AddWithValue("@Referencia", pProveedor.Referencia)
                    myComm.Parameters.AddWithValue("@Municipio", pProveedor.Municipio)
                    myComm.Parameters.AddWithValue("@Estado", pProveedor.Estado)
                    myComm.Parameters.AddWithValue("@Pais", pProveedor.Pais)
                    myComm.Parameters.AddWithValue("@CodigoPostal", pProveedor.CodigoPostal)
                    myComm.Parameters.AddWithValue("@Telefono1", pProveedor.Telefono1)
                    myComm.Parameters.AddWithValue("@Telefono2", pProveedor.Telefono2)
                    myComm.Parameters.AddWithValue("@Fax", pProveedor.Fax)
                    myComm.Parameters.AddWithValue("@CorreoElect", pProveedor.CorreoElect)
                    myComm.Parameters.AddWithValue("@DirInternet", pProveedor.DirInternet)
                    myComm.Parameters.AddWithValue("@Contacto", pProveedor.Contacto)
                    myComm.Parameters.AddWithValue("@Zona", pProveedor.Zona)
                    myComm.Parameters.AddWithValue("@Grupo", pProveedor.Grupo)
                    Select Case pProveedor.Status
                        Case ProveedoresBE.EstatusProveedor.Activo
                            myComm.Parameters.AddWithValue("@Status", "A")
                        Case ProveedoresBE.EstatusProveedor.Inactivo
                            myComm.Parameters.AddWithValue("@Status", "I")
                        Case Else
                            myComm.Parameters.AddWithValue("@Status", "I")
                    End Select
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function actualizaProveedor(ByVal pProveedor As ProveedoresBE) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE proveedores " & _
                                            "SET RfcPro = @RfcPro, RazonSocial = @RazonSocial, " & _
                                                 "Calle = @Calle, NoExterior = @NoExterior, NoInterior = @NoInterior, Colonia = @Colonia, " &
                                                 "Localidad = @Localidad, Referencia = @Referencia, Municipio = @Municipio , Estado = @Estado, Pais = @Pais, " & _
                                                 "CodigoPostal = @CodigoPostal, Telefono1 = @Telefono1, Telefono2 = @Telefono2, Fax = @Fax, " &
                                                 "CorreoElect = @CorreoElect, DirInternet = @DirInternet, Contacto = @Contacto, Zona = @Zona, " &
                                                 "Grupo = @Grupo, Status = @Status " & _
                                                 "WHERE CvePro = @CvePro; "
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@RfcPro", pProveedor.RfcPro)
                    myComm.Parameters.AddWithValue("@RazonSocial", pProveedor.RazonSocial)
                    myComm.Parameters.AddWithValue("@Calle", pProveedor.Calle)
                    myComm.Parameters.AddWithValue("@NoExterior", pProveedor.NoExterior)
                    myComm.Parameters.AddWithValue("@NoInterior", pProveedor.NoInterior)
                    myComm.Parameters.AddWithValue("@Colonia", pProveedor.Colonia)
                    myComm.Parameters.AddWithValue("@Localidad", pProveedor.Localidad)
                    myComm.Parameters.AddWithValue("@Referencia", pProveedor.Referencia)
                    myComm.Parameters.AddWithValue("@Municipio", pProveedor.Municipio)
                    myComm.Parameters.AddWithValue("@Estado", pProveedor.Estado)
                    myComm.Parameters.AddWithValue("@Pais", pProveedor.Pais)
                    myComm.Parameters.AddWithValue("@CodigoPostal", pProveedor.CodigoPostal)
                    myComm.Parameters.AddWithValue("@Telefono1", pProveedor.Telefono1)
                    myComm.Parameters.AddWithValue("@Telefono2", pProveedor.Telefono2)
                    myComm.Parameters.AddWithValue("@Fax", pProveedor.Fax)
                    myComm.Parameters.AddWithValue("@CorreoElect", pProveedor.CorreoElect)
                    myComm.Parameters.AddWithValue("@DirInternet", pProveedor.DirInternet)
                    myComm.Parameters.AddWithValue("@Contacto", pProveedor.Contacto)
                    myComm.Parameters.AddWithValue("@Zona", pProveedor.Zona)
                    myComm.Parameters.AddWithValue("@Grupo", pProveedor.Grupo)
                    Select Case pProveedor.Status
                        Case ProveedoresBE.EstatusProveedor.Activo
                            myComm.Parameters.AddWithValue("@Status", "A")
                        Case ProveedoresBE.EstatusProveedor.Inactivo
                            myComm.Parameters.AddWithValue("@Status", "I")
                        Case Else
                            myComm.Parameters.AddWithValue("@Status", "I")
                    End Select
                    myComm.Parameters.AddWithValue("@CvePro", pProveedor.CvePro)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Function cambiaStatus(ByVal CvePro As String, ByVal setActivo As Boolean) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE proveedores " & _
                                            "SET Status = @Status " & _
                                            "WHERE CvePro = @CvePro;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    If (setActivo) Then
                        myComm.Parameters.AddWithValue("@Status", "A")
                    Else
                        myComm.Parameters.AddWithValue("@Status", "I")
                    End If
                    myComm.Parameters.AddWithValue("@CvePro", CvePro)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function getProveedor(ByVal CvePro As String) As ProveedoresBE
        Dim tmpProveedor As ProveedoresBE
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdPro,CvePro,RfcPro, RazonSocial, " & _
                                                "Calle, NoExterior, NoInterior, Colonia, Localidad, Referencia, Municipio, Estado, Pais, CodigoPostal, " & _
                                                "Telefono1, Telefono2, Fax, CorreoElect, DirInternet, Contacto, Zona, Grupo, Status " & _
                                            "FROM proveedores " & _
                                            "WHERE CvePro = @CvePro;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@CvePro", CvePro)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpProveedor = ProveedoresFact.Build(myDR)
                        Else
                            tmpProveedor = Nothing
                        End If
                    End Using
                End Using
            End Using
            Return tmpProveedor

        Catch ex As Exception
            Throw
        Finally
            tmpProveedor = Nothing
        End Try
    End Function

    Public Function getListadoProveedores(ByVal pRfcPro As String, ByVal pCorreoElect As String, ByVal pRazonSocial As String,
                                          ByVal estatus As Enumeraciones.estatusParaBusquedas,
                                          ByVal pRegistroInicial As Integer, ByVal pPageSize As Integer,
                                          ByVal pSortColumn As Enumeraciones.campoOrdenamiento, ByVal pSortDirection As Enumeraciones.tipoOrdenamiento,
                                          ByRef pTotalRegistros As Integer) As List(Of ProveedoresBE)
        pTotalRegistros = 0
        Dim ProveedorLs As List(Of ProveedoresBE) = New List(Of ProveedoresBE)
        Dim strQry As String = ""

        Select Case estatus
            Case Enumeraciones.estatusParaBusquedas.Todos
                'No se aplica filtro
            Case Enumeraciones.estatusParaBusquedas.Activos
                strQry &= " AND Status = 'A' "
            Case Enumeraciones.estatusParaBusquedas.Inactivos
                strQry &= " AND Status = 'I' "
        End Select

        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT SQL_CALC_FOUND_ROWS IdPro,CvePro,RfcPro, RazonSocial, " & _
                                                "Calle, NoExterior, NoInterior, Colonia, Localidad, Referencia, Municipio, Estado, Pais, CodigoPostal, " & _
                                                "Telefono1, Telefono2, Fax, CorreoElect, DirInternet, Contacto, Zona, Grupo, Status " & _
                                            "FROM proveedores " & _
                                            "WHERE RfcPro LIKE @RfcPro " &
                                                "AND CorreoElect LIKE @CorreoElect " &
                                                "AND RazonSocial LIKE @RazonSocial " &
                                                strQry &
                                            Enumeraciones.getOrderByStatement(Enumeraciones.catalogo.Proveedores, pSortColumn, pSortDirection) &
                                            " LIMIT @regInic, @pageSize;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@RfcPro", "%" & pRfcPro & "%")
                    myComm.Parameters.AddWithValue("@CorreoElect", "%" & pCorreoElect & "%")
                    myComm.Parameters.AddWithValue("@RazonSocial", "%" & pRazonSocial & "%")
                    myComm.Parameters.AddWithValue("@regInic", pRegistroInicial)
                    myComm.Parameters.AddWithValue("@pageSize", pPageSize)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        While myDR.Read()
                            Dim Proveedor As ProveedoresBE = ProveedoresFact.Build(myDR)
                            If (Not Proveedor Is Nothing) Then
                                ProveedorLs.Add(Proveedor)
                            End If
                            Proveedor = Nothing
                        End While
                    End Using
                    Using myComm2 As MySqlCommand = myConn.CreateCommand()
                        myComm2.CommandType = CommandType.Text
                        myComm2.CommandText = "SELECT FOUND_ROWS();"
                        pTotalRegistros = Convert.ToInt32(myComm2.ExecuteScalar())
                    End Using
                End Using
            End Using
            Return ProveedorLs
        Catch ex As Exception
            Throw
        Finally
            ProveedorLs = Nothing
            strQry = Nothing
        End Try
    End Function

#End Region

    

End Class
